﻿SET NOCOUNT ON

--- Create the centraldba Database
USE [master]
GO
IF NOT EXISTS (SELECT TOP 1 * FROM sysdatabases WHERE name = '$(DatabaseName)')
  BEGIN
	DECLARE	@data_directory_name nvarchar(4000), @log_directory_name nvarchar(4000), @sCmd nvarchar(4000)

	--- Determine the base directory for data files
	EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @data_directory_name output, ''
	IF ISNULL(@data_directory_name, '') = ''
	  BEGIN
		EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLDataRoot', @data_directory_name output, ''
		IF NOT ISNULL(@data_directory_name, '') = ''
			SELECT	@data_directory_name = @data_directory_name + '\Data'
	  END

	--- Determine the base directory for log files
	EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @log_directory_name output, ''
	IF ISNULL(@log_directory_name, '') = ''
	  BEGIN
		EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLDataRoot', @log_directory_name output, ''
		IF NOT ISNULL(@log_directory_name, '') = ''
			SELECT	@log_directory_name = @log_directory_name + '\Data'
	  END

	--- Build the file names
	SELECT	@data_directory_name = @data_directory_name + '\$(DatabaseName).mdf', 
			@log_directory_name = @log_directory_name + '\$(DatabaseName)_Log.ldf'

	--- Create the database
	SELECT	@sCmd = '
	CREATE DATABASE [$(DatabaseName)]
	  ON  PRIMARY 
		(
			NAME = N''$(DatabaseName)'', 
			FILENAME = N''' + @data_directory_name + ''' , 
			SIZE = 10240KB , 
			MAXSIZE = UNLIMITED, 
			FILEGROWTH = 1024KB 
		)
	  LOG ON 
		(
			NAME = N''$(DatabaseName)_log'', 
			FILENAME = N''' + @log_directory_name + ''' ,
			SIZE = 5120KB , 
			MAXSIZE = 2048GB , 
			FILEGROWTH = 10%
		)'
		
	EXEC (@sCmd)
  END
GO
ALTER DATABASE [$(DatabaseName)] SET TRUSTWORTHY ON
GO



USE [$(DatabaseName)]
GO

/*  Change the db_owner to sa */
EXEC sp_changedbowner 'sa'
GO


/*  Setup the Insufficient Resources Alert  */
DECLARE @sqlAdminName nvarchar(30), @email_address nvarchar(100)
SELECT	@sqlAdminName = '$(AdminEmailAccount)',
		@email_address = @sqlAdminName + '@$(AdminEmailDomainName)'

IF NOT EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = @sqlAdminName)
  BEGIN
	EXEC msdb.dbo.sp_add_operator @name=@sqlAdminName, @enabled=1, @pager_days=0, 
			@weekday_pager_start_time=90000, @weekday_pager_end_time=180000, 
			@saturday_pager_start_time=90000, @saturday_pager_end_time=180000, 
			@sunday_pager_start_time=90000, @sunday_pager_end_time=180000, 
			@email_address=@email_address, @category_name=N'[Uncategorized]'

	PRINT '   Added Operator [' + @sqlAdminName + ']'
  END

--- Set this operator as the Fail Safe Operator
EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator=@sqlAdminName, @notificationmethod=1

IF NOT EXISTS(SELECT TOP 1 name FROM msdb.dbo.sysalerts (NOLOCK) WHERE name = 'Insufficient Resources')
  BEGIN
	EXEC msdb.dbo.sp_add_alert @name=N'Insufficient Resources', 
			@message_id=0, @severity=17, @enabled=1, 
			@delay_between_responses=900, @include_event_description_in=1, 
			@category_name=N'[Uncategorized]';

	DECLARE @curr_date INT, @curr_time INT;
	SELECT	@curr_date = CONVERT(INT, CONVERT(CHAR, GETDATE(), 112)), 
			@curr_time = (DATEPART(hh, GETDATE()) * 10000) + (DATEPART(mi, GETDATE()) * 100) + (DATEPART(ss, GETDATE()));
	EXEC msdb.dbo.sp_update_alert @name = N'Insufficient Resources', @count_reset_date = @curr_date, @count_reset_time = @curr_time, @occurrence_count = 0;

	PRINT '   Added Alert [Insufficient Resources]'
  END

IF EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = @sqlAdminName)
	AND EXISTS(SELECT TOP 1 name FROM msdb.dbo.sysalerts (NOLOCK) WHERE name = 'Insufficient Resources')
	AND NOT EXISTS(SELECT TOP 1 alert_id FROM msdb.dbo.sysnotifications 
					WHERE alert_id = (SELECT id FROM msdb.dbo.sysalerts WHERE name = 'Insufficient Resources') 
					AND operator_id = (SELECT id FROM msdb.dbo.sysoperators WHERE name = @sqlAdminName))
  BEGIN
	EXEC msdb.dbo.sp_add_notification @alert_name=N'Insufficient Resources', @operator_name=@sqlAdminName, @notification_method = 1;

	PRINT '   Added Operator [' + @sqlAdminName + '] to Alert [Insufficient Resources]'
  END
GO

SET NOCOUNT OFF